CREATE        PROCEDURE amsp_GetChildMenuItems
  @NavMenuID integer,
  @ContactID numeric
AS
BEGIN

  DECLARE
    @counter integer

  create table #temp (NavMenuID integer)

  /*
  ** First insert selfe
  */

  INSERT INTO #temp
  SELECT NavMenuID
  FROM Nav_Menu
  WHERE NavMenuID = @NavMenuID
  
  /*
  ** As long as there are new children, keep adding them to #temp.
  ** To make sure a bad nav_menu item doesn't lock the server, we also
  ** limit this to 50 iterations.
  */
  SET @counter = 0
  WHILE @@rowCount > 0 AND @counter < 50 BEGIN
    SET @counter = @counter + 1
    INSERT INTO #temp
    SELECT a.NavMenuID
      FROM Nav_Menu a, #temp b
     WHERE a.ParentNavMenuID = b.NavMenuID
       AND a.NavMenuID NOT IN (SELECT NavMenuID FROM #temp)
  END

  /*
  ** Return our results
  */

  SELECT a.*,
         b.ContentEditorFlag,
         b.ContentApproverFlag,
         b.NavCreatorFlag,
         b.NavEditorFlag,
         b.CustomPageFlag,
         b.LayoutFlag,
         b.UploadFlag,
         b.EditorFlag,
         b.ComponentScriptFlag,
         c.HideFlag AS ParentHideFlag,
        (SELECT count(*)
           FROM Nav_Menu z
          WHERE z.SortOrder > a.SortOrder
            AND z.SortOrder <
               (SELECT IsNull(Min(x.SortOrder),99999)
                  FROM Nav_Menu x
                 WHERE x.SortOrder > a.SortOrder
                   AND x.CategoryDepth <= a.CategoryDepth)) AS DescendantCount,
        (SELECT count(*)
           FROM Nav_Menu z
          WHERE z.SortOrder > a.SortOrder
            AND z.SortOrder <
               (SELECT IsNull(Min(x.SortOrder),99999)
                  FROM Nav_Menu x
                 WHERE x.SortOrder > a.SortOrder
                   AND x.CategoryDepth <= a.CategoryDepth)
            AND z.ContentAuthorityGroupID IN (SELECT ContentAuthorityGroupID
                        FROM Content_Authority_Producer
                        WHERE ContactID = @ContactID)) AS AuthorizedDescendantCount,
         NULL AS AncestoryList,
         NULL As AncestoryHideFlag,
         (SELECT count(*)
            FROM Content z
           WHERE z.NavMenuID = a.NavMenuID
             AND z.WorkflowStatusCode = 'W') AS WorkingContentCount,
         (SELECT count(*)
            FROM Content z
           WHERE z.NavMenuID = a.NavMenuID
             AND (z.WorkflowStatusCode = 'D' OR z.WorkflowStatusCode = 'E')) AS PendingContentCount,
         (SELECT count(*)
            FROM Content z
           WHERE z.NavMenuID = a.NavMenuID
             AND z.WorkflowStatusCode = 'A') AS ApprovedContentCount,
         (SELECT MAX(ContentID)
            FROM vCurrent_Content z
           WHERE z.NavMenuID = a.NavMenuID
             AND (z.ContentID = a.ContentID OR z.PreviousContentID = a.ContentID)) AS WorkingContentID
    FROM (Nav_Menu a LEFT OUTER JOIN Content_Authority_Producer b
      ON a.ContentAuthorityGroupID = b.ContentAuthorityGroupID
     AND b.ContactID = @ContactID) LEFT OUTER JOIN Nav_Menu c
      ON a.ParentNavMenuID = c.NavMenuID, #temp t
   WHERE a.NavMenuID = t.NavMenuID
   ORDER BY a.SortOrder

END

GO
GRANT EXECUTE ON  [dbo].[amsp_GetChildMenuItems] TO [IMIS]
GO